﻿using Meiam.System.Interfaces.IService;
using Meiam.System.Model.Entity;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Meiam.System.Interfaces.Service
{
	public class MachineConfirmationService : SqlSugarBase<MachineConfirmation>, IMachineConfirmationService
	{
		public MachineConfirmationService(IConfiguration _configuration)
		{
			base.connectionString = _configuration.GetConnectionString("DB1");
		}

		/// <summary>
		/// 切片关键指标一车间A区
		/// </summary>
		/// <returns></returns>
		public List<MachineConfirmation> GetMachineConfirmationWorkshopOneAreaA4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'一车间A区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}

		public List<MachineConfirmation> GetMachineConfirmationWorkshopOneAreaB4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'一车间B区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}


		public List<MachineConfirmation> GetMachineConfirmationWorkshopTwoAreaA4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'二车间A区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}

		public List<MachineConfirmation> GetMachineConfirmationWorkshopTwoAreaB4H()
			{
				string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
		string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

		string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'二车间B区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
	}



		public List<MachineConfirmation> GetMachineConfirmationWorkshopThreeAreaA4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'三车间A区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}

		public List<MachineConfirmation> GetMachineConfirmationWorkshopThreeAreaB4H()
        {
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'三车间B区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}


		public List<MachineConfirmation> GetMachineConfirmationWorkshopFourAreaA4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'四车间A区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}

		public List<MachineConfirmation> GetMachineConfirmationWorkshopFourAreaB4H()
		{
			string startTime = DateTime.Now.AddHours(-4).ToString("yyyy-MM-dd HH:mm:ss");
			string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

			string sql = @"WITH
	A AS (
	SELECT
	ccd.CONTAINERNAME,
	TO_CHAR(ccd.RECORDTIME, 'yyyy-mm-dd hh24:mi:ss') RECORETIME,
	CASE
	WHEN ot.NOTES LIKE '%一车间A区%' THEN '一车间A区'
	WHEN ot.NOTES LIKE '%一车间B区%' THEN '一车间B区'
	WHEN ot.NOTES LIKE '%二车间A区%' THEN '二车间A区'
	WHEN ot.NOTES LIKE '%二车间B区%' THEN '二车间B区'
	WHEN ot.NOTES LIKE '%三车间A区%' THEN '三车间A区'
	WHEN ot.NOTES LIKE '%三车间B区%' THEN '三车间B区'
	WHEN ot.NOTES LIKE '%四车间A区%' THEN '四车间A区'
	WHEN ot.NOTES LIKE '%四车间B区%' THEN '四车间B区'
	ELSE ot.NOTES END
	WORKSHOP,
	p.CUSIZE CUSIZE,
	TO_NUMBER(altb.CUREALQTY) THEORYPIECE,
	NVL(ccd.CUAGRADERXH,0) CUAGRADERXH,
	NVL(ccd.CUAGRADERQQ,0) CUAGRADERQQ,
	NVL(ccd.CUBGRADERXH,0) CUBGRADERXH,
	NVL(ccd.CUBGRADERQQ,0) CUBGRADERQQ,
	NVL(ccd.CUBGRADERHB,0) CUBGRADERHB,
	NVL(ccd.CUBGRADERTTV,0) CUBGRADERTTV,
	NVL(ccd.CUCGRADERBG,0) CUCGRADERBG,
	NVL(ccd.CUCGRADERCC,0) CUCGRADERCC,
	NVL(ccd.CUAGRADERBB,0) CUAGRADERBB,
	NVL(ccd.CUBGRADERGL,0) CUBGRADERGL,
	NVL(ccd.CUDGRADERYL,0) CUDGRADERYL,
	NVL(ccd.CUGRADERQJ,0) CUGRADERQJ,
	c.CONTAINERID 
 FROM CUCONTAINERGRADERRECORD ccd 
 LEFT JOIN MFGORDER o
	ON ccd.cuworkorder = o.mfgordername
 LEFT JOIN ORDERTYPE ot
	ON o.ORDERTYPEID = ot.ORDERTYPEID
 LEFT JOIN CONTAINER c
	ON ccd.CONTAINERNAME = c.CONTAINERNAME
LEFT JOIN PRODUCT p
	ON c.PRODUCTID = p.PRODUCTID
 LEFT JOIN A_LOTATTRIBUTES altb
	ON c.CONTAINERID = altb.CONTAINERID 
WHERE ccd.RECORDTIME BETWEEN to_date('" + startTime + @"','yyyy-MM-dd hh24:mi:ss') AND to_date('" + endTime + @"','yyyy-MM-dd hh24:mi:ss') 
AND REGEXP_LIKE(ot.NOTES,'四车间B区') 
AND ccd.SPECNAME IN ('分选') 
ORDER BY ccd.RECORDTIME 
	),
	B AS (
	SELECT 
		HM.CONTAINERNAME, 
		MAX(HM.resourcename) AS CUTSLICERESOURCENAME 
	FROM historymainline HM 
	WHERE HM.SPECNAME = '切片' AND HM.CDONAME ='TrackOutLot'  
	GROUP BY HM.CONTAINERNAME 
	)
SELECT
	*
FROM
(SELECT
	A.WORKSHOP,
	B.CUTSLICERESOURCENAME,
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
INNER JOIN B
	ON A.CONTAINERNAME = B.CONTAINERNAME
GROUP BY A.WORKSHOP,B.CUTSLICERESOURCENAME
ORDER BY B.CUTSLICERESOURCENAME)
UNION ALL
SELECT
	A.CUSIZE,
	'-',
	SUM(A.THEORYPIECE) THEORYPIECE,
	SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC) XH,
	TO_CHAR(ROUND(SUM(A.CUAGRADERXH+A.CUAGRADERQQ+A.CUBGRADERXH+A.CUBGRADERQQ+A.CUCGRADERCC)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' XH_PER,
	SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG) HB,
	TO_CHAR(ROUND(SUM(A.CUBGRADERHB+A.CUBGRADERTTV+A.CUCGRADERBG)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' HB_PER,
	SUM(A.CUAGRADERBB) ABB,
	TO_CHAR(ROUND(SUM(A.CUAGRADERBB)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' ABB_PER,
	SUM(A.CUBGRADERGL) BGL,
	TO_CHAR(ROUND(SUM(A.CUBGRADERGL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' BGL_PER,
	SUM(A.CUDGRADERYL) DYL,
	TO_CHAR(ROUND(SUM(A.CUDGRADERYL)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DYL_PER,
	SUM(A.CUGRADERQJ) DQJ,
	TO_CHAR(ROUND(SUM(A.CUGRADERQJ)/SUM(A.THEORYPIECE),4)*100, 'fm90.09') || '%' DQJ_PER
FROM A
GROUP BY A.CUSIZE";

			return base.Query(sql);
		}
	}
}
